package com.dong.dao;

import com.dong.entity.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao1 {

    public void addUser(User user){

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn= null;
        PreparedStatement ps=null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/video1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","1234");

            //对sql语句进行预编译
            ps= conn.prepareStatement("insert into users(user_name,user_pwd,user_status,user_img)values(?,?,?,?)");
            //设置四个占位符的值(个数顺序和？需要匹配)
            ps.setString(1,user.getUser_name());
            ps.setString(2,user.getUser_pwd());
            ps.setString(3,user.getUser_status());
            ps.setString(4,user.getUser_img());
            ps.executeUpdate();//执行
            System.out.println("添加成功");


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally{
            try {
                ps.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
    public void deleteUser(){

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn= null;
        PreparedStatement ps=null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/video1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","1234");

            //对sql语句进行预编译
            ps= conn.prepareStatement("delete from users where user_id=?");
            ps.setInt(1,10);
            ps.executeUpdate();
            System.out.println("删除完成");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally{
            try {
                ps.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
    public void updateUser(){

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn= null;
        PreparedStatement ps=null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/video1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","1234");

            //对sql语句进行预编译
            ps= conn.prepareStatement("update users set user_name=?,user_pwd=? where user_id=?");
            //设置四个占位符的值(个数顺序和？需要匹配)
            ps.setString(1,"潘金莲");
            ps.setString(2,"520");
            ps.setInt(3,9);
            ps.executeUpdate();//执行
            System.out.println("更新完成");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally{
            try {
                ps.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }
    public List<User> queryUser(){

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn= null;
        PreparedStatement ps=null;
         List<User> list = new ArrayList<>();
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/video1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","1234");

            //对sql语句进行预编译
            ps= conn.prepareStatement("select *from users");
            //让数据库服务器执行sql语句，返回结果集
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String pwd = resultSet.getString(3);
                String staus = resultSet.getString(4);
                String img = resultSet.getString(5);
                User user = new User(id, name, pwd, staus, img);
                   list.add(user);
                System.out.println("用户编号:"+ id +"用户名:"+ name +"密码:"+ pwd+"状态:"+staus+"图片"+img);
            }
            ps.executeUpdate();//执行



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally{
            try {
                ps.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return list;
        }


    }
    public User queryUserById(Integer id){

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn= null;
        PreparedStatement ps=null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/video1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","1234");

            //对sql语句进行预编译
            ps= conn.prepareStatement("select *from users where user_id=?");
            ps.setInt(1,id);
            //让数据库服务器执行sql语句，返回结果集
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString(2);
                String pwd = resultSet.getString(3);
                String staus = resultSet.getString(4);
                String img = resultSet.getString(5);
                User user = new User(id, name, pwd, staus, img);
                System.out.println("用户编号:" + id + "用户名:" + name + "密码:" + pwd + "状态:" + staus + "图片" + img);
            }

            ps.executeUpdate();//执行



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally{
            try {
                ps.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

            return null;
    }

//    public List<Goods> selectGoodsByCondition(Goods queryBean,int currentPage,int pageSize){
//
//        /*根据queryBean中封装的信息进行判断*/
//
//        List<Goods> l=new ArrayList<Goods>();
//        Connection conn=null;
//        PreparedStatement ps=null;
//        ResultSet rs=null;
//        try {
//            conn=DBTools.getConnection();
//            //拼接sql语句
//            StringBuilder sb=new StringBuilder("select goods_id,goods.type_id,supplier_id,goods_code,goods_name,goods_count,goods_price,goods_img,type_name,type_code from goods left join types on goods.type_id=types.type_id where 1=1");
//            if(queryBean.getGoodsName()!=null&&!queryBean.getGoodsName().equals("")){
//
//                sb.append(" and goods_name like ?");
//            }
//
//            if(queryBean.getTypeId()!=null){
//
//                sb.append(" and type_id = ?");
//            }
//
//            sb.append(" limit ?,?");
//
//            System.out.println(sb.toString());
//            ps=conn.prepareStatement(sb.toString());
//            //设置占位符的值
//            int count=0;
//
//
//            if(queryBean.getGoodsName()!=null&&!queryBean.getGoodsName().equals("")){
//                count++;
//                ps.setString(count, "%"+queryBean.getGoodsName()+"%");
//
//            }
//
//            if(queryBean.getTypeId()!=null){
//                count++;
//                ps.setInt(count, queryBean.getTypeId());
//            }
//
//            //设置limit后两个？的值
//            count++;
//            ps.setInt(count, (currentPage-1)*pageSize);
//            count++;
//            ps.setInt(count, pageSize);
//
//            rs=ps.executeQuery();
//            while(rs.next()){
//
//                Integer goodsId=rs.getInt("goods_id");
//                Integer typeId=rs.getInt("type_id");
//                Integer supplierId=rs.getInt("supplier_id");
//                String goodsCode=rs.getString("goods_code");
//                String goodsName=rs.getString("goods_name");
//                Integer goodsCount=rs.getInt("goods_count");
//                String goodsImg=rs.getString("goods_img");
//                Float goodsPrice=rs.getFloat("goods_price");
//                String typeName=rs.getString("type_name");
//                Type t=new Type();
//                t.setTypeName(typeName);
//                Goods goods=new Goods(goodsId, typeId, supplierId, goodsCode, goodsName, goodsCount, goodsPrice, goodsImg);
//                goods.setType(t);
//
//                l.add(goods);
//            }
//
//            return l;
//
//        } catch (SQLException e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }
//        finally{
//            DBTools.close(conn, ps, rs);
//
//        }
//        return l;
//
//    }
//}

    public List<User> queryUserByConnection(User userQuery){

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        List<User> list = new ArrayList<User>();
        Connection conn= null;
        PreparedStatement ps=null;
        ResultSet resultSet=null;

        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/video1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","1234");
           String sql="select *from users where 1=1";//原始的sql语句，1=1保证了语法的正确性，又不影响sql语句的查询结果
//            StringBuilder sb = new StringBuilder("select *from users where 1=1");
            if(userQuery.getUser_name()!=null&&!userQuery.equals("")) {
                sql += " and users.user_name=?";
//                sb.append(" and user_name like ?");
            }
            if(userQuery.getUser_status()!=null&&!userQuery.equals("")) {
                sql += " and users.user_status=?";
//                sb.append(" and user_status=?");
            }
            System.out.println(sql);
//            System.out.println(sql);
            ps = conn.prepareStatement(sql);

            //设置？的值(有几个)
            int count=0;//问号的索引位置
            if(userQuery.getUser_name()!=null&&!userQuery.equals("")) {
                count++;
//                ps.setString(count,"%"+userQuery.getUser_name()+"%");
                ps.setString(count,userQuery.getUser_name());

            }
            if(userQuery.getUser_status()!=null&&!userQuery.equals("")) {
                count++;
                ps.setString(count, userQuery.getUser_status());
            }
            resultSet = ps.executeQuery();



//            //对sql语句进行预编译
//            ps= conn.prepareStatement("select *from users");
//            //让数据库服务器执行sql语句，返回结果集

            while (resultSet.next()){
                int id = resultSet.getInt("user_id");
                String name = resultSet.getString("user_name");
                String pwd = resultSet.getString("user_pwd");
                String status = resultSet.getString("user_status");
                String img = resultSet.getString("user_img");
                User user = new User(id,name,pwd,status,img);
                 list.add(user);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally{
            try {
                resultSet.close();
                ps.close();
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

        return list;
    }
public List<User>  selectUserByCondition(User userQuery){
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    Connection conn=null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    List<User> users=new ArrayList<User>();
    try {
        conn=DriverManager.getConnection("jdbc:mysql://localhost:3307/video1?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","1234");
        //将sql语句发送给数据库服务器，让数据库服务器进行预先编译，检查sql语句语法及词法的正确性

        StringBuilder sql=new StringBuilder("select * from users where 1=1");//原始的sql语句.1=1保证了语法的正确性，又不影响原始sql语句的查询结果
        //根据查询条件做拼接
        if(userQuery.getUser_name()!=null&&!userQuery.equals("")){

            //sql+=" and user_name like ?";
            sql.append(" and user_name like ?");
        }

        if(userQuery.getUser_status()!=null&&!userQuery.equals("")){
            //sql+=" and user_status = ?";
            sql.append(" and user_status = ?");
        }


        System.out.println(sql);
        ps=conn.prepareStatement(sql.toString());//让服务器进行预编译
        //设置？的值（有几个？号）
        int count=0;//表示？的索引位置
        if(userQuery.getUser_name()!=null&&!userQuery.equals("")){
            count++;
            ps.setString(count,"%"+userQuery.getUser_name()+"%");

        }

        if(userQuery.getUser_status()!=null&&!userQuery.equals("")){
            count++;
            ps.setString(count, userQuery.getUser_status());

        }
//
//            //让数据库服务器执行sql语句,返回结果集
        rs=ps.executeQuery();
        //1.移动指针2.返回值为true，表示当前指针还没有指向最后一行，为false表示已经在最后一行了
        while(rs.next()){
            //右边方法的参数必须和结果集的列名保持一致
            Integer userId=rs.getInt("user_id");
            String userName=rs.getString("user_name");
            String userPwd=rs.getString("user_pwd");
            String userStatus=rs.getString("user_status");
            String userImg=rs.getString("user_img");

            //读取到了一行数据的每一列
            User user=new User(userId, userName, userPwd, userStatus, userImg );
            users.add(user);


        }

    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    finally {

        try {
            rs.close();
            ps.close();
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    return users;
}



    public static void main(String[] args) {
        UserDao1 userDao1=new UserDao1();
//        userDao1.addUser(new User(null, "fgsj", "123456","正常","test"));
//        userDao1.deleteUser();
//        userDao1.updateUser();
//        userDao1.queryUserByConnection(new User(null, "林", "123456","正常","test"));
        User queryBean = new User();
//        queryBean.setUser_name("林");
        queryBean.setUser_status("正常");
        List<User> users = userDao1.queryUserByConnection(queryBean);
        System.out.println(users);
//        userDao1.queryUser();
//        userDao1.queryUserById(9);

    }
}
